﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace ConsoleKit.other
{
    public class SqlHelper : IDisposable
    {
        private SqlConnection connection;
        private string connectionString = ConfigurationManager.ConnectionStrings["DefaultConnectionString"].ConnectionString;
        private string testConnectionString = ConfigurationManager.ConnectionStrings["TestConnectionString"].ConnectionString;

        public SqlHelper()
        {

        }

        public void CreateTable(string queryString)
        {
            connection = new SqlConnection(testConnectionString);
            try
            {
                using (SqlCommand command = new SqlCommand(queryString, connection))
                {
                    if (command.Connection.State != ConnectionState.Open)
                        command.Connection.Open();
                    command.ExecuteNonQuery();
                }
            }
            catch (Exception)
            {

            }
            connection.Close();
            connection.Dispose();
            connection = null;
        }

        public void CreateDatabase(string queryString)
        {
            //TODO: modify it using SqlTransaction   
            connection = new SqlConnection(connectionString);
            try
            {
                using (SqlCommand command = new SqlCommand(queryString, connection))
                {
                    command.Connection.Open();
                    command.ExecuteNonQuery();
                }
            }
            catch (Exception)
            {

            }
            connection.Close();
            connection.Dispose();
            connection = null;
        }

        //classname : IDisposable
        ~SqlHelper()
        {
            Dispose(false);
        }
        /// <summary>
        /// Dispose
        /// </summary>
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        /// <summary>
        /// Dispose Member and Field
        /// </summary>
        /// <param name="disposing"></param>
        protected virtual void Dispose(bool disposing)
        {
            if (disposing)
            {
                if (connection != null)
                {
                    connection.Close();
                    connection = null;
                }
            }
        }
    }
}
